package us.lithub.data;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Handler;
import android.os.Message;
import android.util.Log;

/**
 * The Class GlobalDb.
 */
public class GlobalDb {
	
	private static final String TAG = GlobalDb.class.getSimpleName();
	
	/** The Constant TABLE_GLOBAL_DB. */
	public static final String TABLE_GLOBAL_DB = "global_books";
	
	/** The Constant COLUMN_ID. */
	public static final String COLUMN_ID = "_id";
	
	/** The Constant COLUMN_ISBN. */
	public static final String COLUMN_ISBN = "isbn";
	
	/** The Constant COLUMN_AUTHOR. */
	public static final String COLUMN_AUTHOR = "author";
	
	/** The Constant COLUMN_TITLE. */
	public static final String COLUMN_TITLE = "title";
	
	/** The m database. */
	private SQLiteDatabase mDatabase;
	
	/** The db helper. */
	private GlobalDbOpenHelper dbHelper;
	
	/** The m context. */
	private Context mContext;

	/**
	 * Instantiates a new global db.
	 *
	 * @param context the context
	 */
	public GlobalDb(Context context) {
		dbHelper = new GlobalDbOpenHelper(context);
		mContext = context;
	}

	/**
	 * Open the SQLite database.
	 *
	 * @throws SQLException the sQL exception
	 */
	public void open() throws SQLException {
		mDatabase = dbHelper.getWritableDatabase();
	}

	/**
	 * Close the SQLite database.
	 */
	public void close() {
		dbHelper.close();
	}

	/**
	 * Add a new book to the global library.
	 *
	 * @param isbn the isbn
	 * @param author the author
	 * @param title the title
	 * @return the row ID of the newly inserted row, or -1 if an error occurred
	 */
	public long addBook(String isbn, String author, String title) {
		ContentValues values = new ContentValues();
		values.put(GlobalDb.COLUMN_ISBN, isbn);
		values.put(GlobalDb.COLUMN_AUTHOR, author);
		values.put(GlobalDb.COLUMN_TITLE, title);
		return mDatabase.insert(GlobalDb.TABLE_GLOBAL_DB, null, values);
	}
	
	/**
	 * Add a new book to the global library.
	 *
	 * @param isbn the isbn
	 * @return the long
	 */
	public long addBook(String isbn) {
		Book book = ItemLookup.findProduct(isbn);
		return addBook(isbn, book.getAuthor(), book.getTitle());
	}
	
	/**
	 * Checks whether or not book is already in database by its ISBN.
	 *
	 * @param isbn the isbn
	 * @return Returns true if book is found, false if it hasn't been added yet
	 */
	public boolean bookExists(String isbn){
		String str = "SELECT * from global_books where isbn = '" + isbn + "'";
		Cursor cursor = mDatabase.rawQuery(str, null);
		if( cursor.getCount() == 0 )
			return false;
		else 
			return true;
	}//end bookExists
	
	/**
	 * Download database from web to update cache.
	 *
	 * @param progressHandler the progress handler
	 */
	public void downloadDatabase(Handler progressHandler) {
		dbHelper.downloadDatabase(progressHandler);
	}

	private class GlobalDbOpenHelper extends SQLiteOpenHelper {

		private static final String DB_NAME = "GlobalDB";
		private static final String DB_PATH = "/data/data/us.lithub/databases/";
		private static final int DB_VERSION = 1;
		private static final String DB_CREATE = "CREATE TABLE " + TABLE_GLOBAL_DB + " (" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_ISBN + " TEXT, " + COLUMN_AUTHOR + " TEXT, " + COLUMN_TITLE + " TEXT);";

		public GlobalDbOpenHelper(Context context) {
			super(context, DB_NAME, null, DB_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase database) {
			database.execSQL(DB_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			throw new UnsupportedOperationException();
		}

		public void downloadDatabase(Handler progressHandler) {
			try {
				Log.d(TAG, "Downloading MuniDatabase...");
				getWritableDatabase();
				final URL url = new URL("http://dl.dropbox.com/u/9649335/868/GlobalDB");
				final HttpURLConnection connection = (HttpURLConnection) url.openConnection();
				connection.setRequestProperty("Accept-Encoding", "identity");
				connection.connect();

				if (connection.getResponseCode() / 100 != 2) {
					throw new IOException("Response code = " + connection.getResponseCode());
				}

				int contentLength = connection.getContentLength();
				
				if (contentLength < 1) {
					throw new IOException("Invalid content length of " + contentLength);
				}
				
				final InputStream stream = connection.getInputStream();
				final OutputStream outputStream = new FileOutputStream(DB_PATH + DB_NAME);
				final byte[] buffer = new byte[1024];
				int length = 0;
				int read = 0;

				while ((length = stream.read(buffer)) > 0) {
					read += length;
					Message msg = new Message();
					msg.arg1 = contentLength;
					msg.arg2 = read;
					progressHandler.sendMessage(msg);
					outputStream.write(buffer, 0, length);
				}

				outputStream.flush();
				outputStream.close();
				stream.close();
				Log.d(TAG, "DONE Downloading MuniDatabase");
			} catch (Exception e) {
				Log.e(TAG, "EXCEPTION Downloading MuniDatabase");
				Log.e(TAG, e.getMessage());
			} finally {
				close();
			}
		}
		
		public void deleteDatabase(Context context) {
			context.deleteDatabase(DB_NAME);
		}
	}

	/**
	 * Perform a hard reset on the database by deleting the current one and downloading a fresh copy
	 *
	 * @param handler the handler
	 */
	public void hardReset(final Handler handler) {
		new Thread() {
			public void run() {
				dbHelper.deleteDatabase(mContext);
				dbHelper.downloadDatabase(handler);
			}
		}.start();
	}
}
